﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Logistics.DataManage.OracleDb;

namespace Logistics.DataManage.Server
{
    public class RemoteDataOracleService : IRemoteDateService
    {

        public int GetUserState(string userName, string psd)
        {
            //String sql = "select DelUser from Users where  UserName='" + userName + "' and UserPwd='" + psd + "'";
            String sql = "select isused from Users where  userid='" + userName + "' and UserPwd='" + psd + "'";
            Object obj = OracleClient.Handler.ExecuteScalar(sql);
            if (obj != null)
            {

                //string delState = obj.ToString();
                //if (String.IsNullOrEmpty(delState))
                //return 1;
                if (obj.ToString().Equals("1"))
                    return 1;
                else
                    return 0;//已停用的用户
            }
            return -1;//用户不存在
        }

        /// <summary>
        /// 申请注册（C/S专用）
        /// </summary>
        /// >MAC地址
        /// 硬盘型号
        /// CPU型号
        /// IP
        /// 主机名
        /// 创建人
        ///备注
        /// <returns>成功：1；失败：0</returns>
        public int ApplicationRegedit(List<string> list)
        {
            int regId = 0;
            try
            {
                //此处未做其它处理，应付一下算了
                DataSet ds = OracleClient.Handler.ExecuteQuery("select max(RegisterID) from UsersMachineRegister");
                if (ds != null && ds.Tables.Count > 0)
                {
                    regId = Convert.ToInt32(ds.Tables[0].Rows[0][0]) + 1;
                }
            }
            catch
            {
                throw new Exception("注册信息失败，请重新注册");
            }
            string mac = list[0];
            string hdId = list[1];
            string cpu = list[2];
            string ip = list[3];
            string hostUser = list[4];
            string createUser = list[5];
            string remark = list[6];
            string colums = "(REGISTERID,USERID,REGISTERNAME,ISUSED,MAC,REGISTERDATE,COLUMN2)";

            //string colums = "(REGISTERID,IP,COUNTINDEX,ISUSED,REGISTERNAME,NOTE,COLUMN1,REGISTERDATE,COLUMN2,REGISTERCONTENT,COLUMN3,COLUMN4	,COLUMN5,MAC,STATE,USERID)";

            string insertSQL = "insert into UsersMachineRegister " + colums + " values (?,?,?,?,?,?,?)";

            ParameterizedOraSQL PSQL = ParameterizedOraSQL.AdaptSQLAnonymousParams(insertSQL, regId, createUser, createUser, "0", mac, DateTime.Now, remark);

            try
            {
                return OracleClient.Handler.ExecuteNonQuery(PSQL.SQL, PSQL.Parameters);
            }
            catch
            {
                return 0;
            }
        }

        public DataSet GetSynchronous(string where, string FieldSet, string tabTable)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT " + FieldSet + " FROM " + tabTable + " ");
            if (!string.IsNullOrEmpty(where))
            {
                sb.Append(" where " + where);
            }
            return OracleClient.Handler.ExecuteQuery(sb.ToString());
        }

        public DataSet GetCustomers(string SiteId, DateTime dateStart, DateTime dateEnd)
        {
            //String startSiteFilter = "select siteName from SiteInfo where SiteID='" + SiteId + "'";
            //String siteNameFilter = "select EndSite from Route where StartSite=(" + startSiteFilter + ")  union all " + startSiteFilter;
            //String NetworkFilter = "select SiteID from SiteInfo where SiteName in (" + siteNameFilter + ")";
            //string where = "EditDate>='" + startTime + "' and EditDate<'" + endTime + "' and ((DelSign='0') or (DelSign is null)) and NetworkID in (" + NetworkFilter + ")";
            String SQL = "select * from Customs where IsChecked='1' and ((EditDate>=? and EditDate<?) or(CreateDate>=? and CreateDate<?)) and SiteName=?"; //SiteName字段存的是SiteId
            ParameterizedOraSQL PSQL = ParameterizedOraSQL.AdaptSQLAnonymousParams(SQL, dateStart, dateEnd, dateStart, dateEnd, SiteId);
            DataSet dsRtn = OracleClient.Handler.ExecuteQuery(PSQL.SQL, PSQL.Parameters);
            return dsRtn;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="SiteId"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataSet GetCustomers(string SiteId, int pageIndex, int pageSize)
        {
            int start = (pageSize * pageIndex) + 1;
            int end = pageSize * (pageIndex + 1);
            String coreSQL = "select * from Customs where column1='0' and (networkname in(select networkid from networkinfo where siteid = ?) ";//SiteName字段存的是SiteId
            coreSQL += "or networkName in(select networkid from networkinfo where siteid in(select EndSiteID from Routes where BeginSiteID=?)))";//SiteName字段存的是SiteId
            String pagedSQLTemplate = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM ({0}) A WHERE A.IsChecked='1' and ROWNUM <= {1}) WHERE RN >= {2}";
            String PagedSQL = String.Format(pagedSQLTemplate, coreSQL, end, start);
            ParameterizedOraSQL PSQL = ParameterizedOraSQL.AdaptSQLAnonymousParams(PagedSQL, SiteId, SiteId);
            DataSet dsRtn = OracleClient.Handler.ExecuteQuery(PSQL.SQL, PSQL.Parameters);
            return dsRtn;
        }
        /// <summary>
        /// 获取总数
        /// </summary>
        /// <param name="SiteId"></param>
        /// <returns></returns>
        public int GetCustomerCount(string SiteId)
        {
            String sql = "select Count(*) from Customs where IsChecked='1' and column1='0' and (networkname in (select networkid from networkinfo where siteid=?) ";
            sql += "or (networkname in(select networkid from networkinfo where siteid in(select EndSiteID from Routes where BeginSiteID=?))))";
            ParameterizedOraSQL PSQL = ParameterizedOraSQL.AdaptSQLAnonymousParams(sql, SiteId, SiteId);
            Object oCount = OracleClient.Handler.ExecuteScalar(PSQL.SQL, PSQL.Parameters);
            return Convert.ToInt32(oCount);
        }

        public String[] UploadBills(DataSet BillsData)
        {
            List<String> BillIds = new List<string>();
            if (BillsData != null && BillsData.Tables[0].Rows.Count > 0)
            {
                DataSet ds = OracleClient.Handler.ExecuteQuery("select * from WayBillInfo where 1=2");
                DataTable TemplateDt = ds.Tables[0];
                IList<String> colNames = MappingTargetColumns(TemplateDt, BillsData.Tables[0]);
                foreach (DataRow row in BillsData.Tables[0].Rows)
                {
                    String FieldNames = "";
                    String values = "";
                    List<object> parameters = new List<object>();
                    foreach (String colName in colNames)
                    {
                        FieldNames += colName + ",";
                        values += "?,";
                        Object oval = row[colName];
                        parameters.Add(oval);
                    }
                    FieldNames = FieldNames.TrimEnd(',');
                    values = values.TrimEnd(',');
                    String sql = "INSERT INTO WayBillInfo (" + FieldNames + ") VALUES (" + values + ")";
                    ParameterizedOraSQL PSQL = ParameterizedOraSQL.AdaptSQLAnonymousParams(sql, parameters.ToArray());
                    try
                    {
                        int result = OracleClient.Handler.ExecuteNonQuery(PSQL.SQL, PSQL.Parameters);
                        if (result > 0)
                        {
                            BillIds.Add(Convert.ToString(row["WaybillID"]));
                        }
                    }
                    catch (Exception ex)
                    {
                        //继续
                        Console.WriteLine(ex.ToString());
                        String id = Convert.ToString(row["WaybillID"]);
                        Logistics.Utils.LogUtils.WriteError("上传数据失败,ID=" + id + ",Details:" + ex.ToString());
                        continue;
                    }
                }
            }
            return BillIds.ToArray();
        }
        public String[] UpLoadLogs(DataSet ds)
        {
            List<String> logIds = new List<string>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataSet dsx = OracleClient.Handler.ExecuteQuery("select * from WaybillLog where 1=2");
                DataTable TemplateDt = dsx.Tables[0];
                IList<String> colNames = MappingTargetColumns(TemplateDt, ds.Tables[0]);
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    String FieldNames = "";
                    String values = "";
                    List<object> parameters = new List<object>();
                    foreach (String colName in colNames)
                    {
                        FieldNames += colName + ",";
                        values += "?,";
                        Object oval = row[colName];
                        parameters.Add(oval);
                    }
                    FieldNames = FieldNames.TrimEnd(',');
                    values = values.TrimEnd(',');
                    String sql = "INSERT INTO WaybillLog (" + FieldNames + ") VALUES (" + values + ")";
                    ParameterizedOraSQL PSQL = ParameterizedOraSQL.AdaptSQLAnonymousParams(sql, parameters.ToArray());
                    try
                    {
                        int result = OracleClient.Handler.ExecuteNonQuery(PSQL.SQL, PSQL.Parameters);
                        if (result > 0)
                        {
                            logIds.Add(Convert.ToString(row["LogID"]));
                        }
                    }
                    catch (Exception ex)
                    {
                        //继续
                        Console.WriteLine(ex.ToString());
                        continue;
                    }
                }
            }
            return logIds.ToArray();
        }

        /// <summary>
        /// 获取相匹配的列名称,防止数据表结构不一致
        /// </summary>
        /// <param name="TemplateDT"></param>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private static IList<String> MappingTargetColumns(DataTable TemplateDT, DataTable dataTable)
        {
            IList<String> list = new List<String>();
            foreach (DataColumn col in TemplateDT.Columns)
            {
                String colName = col.ColumnName;
                foreach (DataColumn colx in dataTable.Columns)
                {
                    String targetColName = colx.ColumnName;
                    if (String.Compare(colName, targetColName, true) == 0)
                        list.Add(targetColName);//这里处理大小写问题
                }
            }
            return list;
        }

    }
}
